Relationships and Tables
What are Relationships?
Your model is likely to contain a large number of tables, many of which are related in some way. End users will want to create queries that return related data from different tables.
For instance, a retail company may have a table that lists products, product color, and product style, another table that lists brands and store locations, and another table that lists sales and net profit.
Say you want to create a list showing sales by product and brand; each of these columns is stored in a different table. To create the required list, there must be a logical relationship (a join) defined between all three of these tables.
These relationships are usually created by a matching a primary key column in one table (the 'owner side' of the join) to an identical foreign key column in another table (the 'inverse side' of the join). For example, the primary key column in the Products table may be 'Product Key'. If this same column exists as a foreign key in the Brands table and the Facts table, we can use it to create a join from Products to Brands and from Products to Facts.
Relationships Diagram
The relationship diagram displays all the tables in the data model, and the relationships between the tables. By default, Pyramid uses heuristics to define relationships according to each table's primary key column.
The direction of each join is indicated by the icons at each end of the join:
: indicates that the join comes from this table; this is the primary table in the join, as it contains the primary key column.
: indicates that the join goes to this table; this is the foreign table in the join, as it contains the foreign key column.
You'll notice that Pyramid automatically adds joins to the model; this is done via heuristic algorithms called 'auto-relationships'. The heuristic model can be changed (you can select from three algorithms) or disabled, and joins can be deleted, added, or edited as required.
- Click here to learn about the heuristic models used for defining auto-relationships.
- Click here to learn more about joins, including join types, and adding, editing, and deleting joins.
- Click here to learn about join key columns.
Navigate the Relationship Diagram
Each table in the model is displayed as a widget in the diagram. Each table widget lists the columns in that table. The relationships between the tables are represented by the lines connecting them.
From the ribbon, you can use the tools grouped under Diagram (red highlight below) to rearrange the relationship diagram.
- Rearrange: if you've moved any tables around on the diagram, the Rearrange function will restore them to their original position on the canvas.
- Scale to Fit: scale the relationship diagram down to fit on the canvas.
- Expand All: expand all tables in the diagram.
- Collapse All: collapse all tables in the diagram.
- Connector Type: select the type of line that will connect joined tables. The default connector type is 'path', but you can change it to 'direct' (straight lines).
- Validate Joins: ensure that the joins are valid.
- Delete: delete the selected join.
Table Properties
Click on a widget's orders to show the properties for that table in the Properties panel (green arrow below).
- Click here to learn about table properties and editing table metadata.
Column Properties
Click a column to show its properties in the Properties panel (green arrow below).
- Click here to learn about columns.
Resize Tables
To resize a table, click on it, then use the small white boxes that appear (see image above) to resize as needed.
Reverse Order
Toggle the table widget's arrows (red arrow below) to reverse the order of the columns.
Collapse and Expand
Toggle the expand/ collapse button (green arrow below) to collapse and expand the table.
Preview
Click a table widget's preview button (blue arrow below) to see a preview of that table in the Preview panel.
Joins
Click on a join (the purple arrow on the following diagram) to open the Join Type context menu (red), and to simultaneously show the join's details in the Properties panel (green):
Copy the join script
Click Copy to copy the script that underlies this join to your clipboard. Tip: You can use this script when testing in the database.
Join context menu
Right-click a join to delete it, switch its direction, or validate it.
Bidirectional Joins
A bidirectional join is used to enable the data to flow in both directions: the data can then flow from the primary table to the foreign table, and from the foreign table to the primary table. This allows users to aggregate a dimension column by a measure. This is in contrast to a typical unidirectional join, where the measure is aggregated by the dimension.
Bidirectional joins are typically not needed, as the database tends to be built with the relationships in mind. Users should take care when adding bidirectional joins, as they affect the aggregation of measures in the query.
Many to Many Joins
When the relationship between the primary and foreign tables is "one-to-many" and doesn't validate due to duplications of unique keys it will replace the relationship to a many-to-many relation and as a result will be a valid connection (the user should understand his data and understand that it can be a many to many relationships).
Currently, this is the only usage of many to many joins, and combine it in the query engine in more meaningful ways.
- Click here to learn more about joins.